# 导入:
import sqlalchemy
from sqlalchemy import Column, String, Integer, DateTime, delete
from utils.sql.db import session, Base
from utils.sql.app.user import User
import time


# 定义User对象:
class Advance(Base):
    # 表的名字:
    __tablename__ = 'advance'

    # 表的结构:
    id = Column(Integer(), primary_key=True, default=None)
    user_id = Column(String(60))
    advance_date = Column(String(20), default=time.strftime("%Y-%m-%d", time.localtime()))
    advance_money = Column(Integer(), default=0)
    create_time = Column(String(20), default=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))


# 添加预支金额
def insert_advance(advance, user_id):
    try:
        # 创建新advance对象:
        new_advance = Advance(
            user_id=user_id,
            advance_date=advance.advance_date,
            advance_money=int(advance.advance_money),
            create_time=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        )
        # 添加到session:
        session.add(new_advance)
        # 提交即保存到数据库:
        session.commit()
        return {
            "code": 200,
            "data": "",
            "message": "数据添加成功"
        }
    except sqlalchemy.exc.IntegrityError as e:
        return {
            "code": 201,
            "data": e,
            "message": "数据已经存在"
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()


# 获取预支金额列表
# 用于分页查询--每页数量
size = 15


def advanceList(user_id: str, query='', current=1):
    advance = None
    try:
        # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
        list = session.query(Advance).order_by(Advance.id.desc()).filter(Advance.user_id == user_id)
        if list:
            start = (current - 1) * size
            end = current * size
            if query:
                # list = list.filter(Advance.advance_money.like(f'%{query}%'))
                list = list.filter(Advance.advance_money == query)
            advance = list.slice(start, end).all()
            return {
                "code": 200,
                "data": advance,
                "message": '查询成功'
            }
    except sqlalchemy.exc.NoResultFound as e:
        return {
            "code": 201,
            "data": "",
            "message": '查询失败'
        }
        session.rollback()
        raise
    finally:
        # 关闭session:
        session.close()
